"""
SQL-like条件字符串解析器
支持将类似SQL的WHERE条件字符串解析为GenericFilter格式
"""

import sqlparse
from sqlparse.sql import Comparison, Parenthesis, Identifier
from sqlparse.tokens import Keyword, String, Number, Name
from typing import List, Union, Optional, Any
import re

from src.models.generic_filter import Condition, FilterGroup, Operator as FilterOperator, LogicalOperator, GenericFilter
from src.utils.logging import get_logger

logger = get_logger(__name__)


class QueryConditionParser:
    """SQL-like条件字符串解析器"""
    
    # SQL操作符到FilterOperator的映射
    OPERATOR_MAP = {
        '=': FilterOperator.EQUALS,
        '!=': FilterOperator.NOT_EQUALS,
        '>': FilterOperator.GREATER_THAN,
        '>=': FilterOperator.GREATER_THAN_OR_EQUAL,
        '<': FilterOperator.LESS_THAN,
        '<=': FilterOperator.LESS_THAN_OR_EQUAL,
        'like': FilterOperator.LIKE,
        'ilike': FilterOperator.ILIKE,
        'in': FilterOperator.IN,
        'not in': FilterOperator.NOT_IN,
        'is null': FilterOperator.IS_NULL,
        'is not null': FilterOperator.IS_NOT_NULL,
        'between': FilterOperator.BETWEEN,
        'contains': FilterOperator.CONTAINS
    }
    
    # 逻辑操作符映射
    LOGICAL_OPERATOR_MAP = {
        'and': LogicalOperator.AND,
        'or': LogicalOperator.OR
    }
    
    @staticmethod
    def parse_condition_string(condition_str: str) -> List[Union[Condition, FilterGroup]]:
        """
        解析SQL-like条件字符串
        
        Args:
            condition_str: 类似SQL的条件字符串，如 "age > 25 AND (name LIKE '%John%' AND status = 'active')"
            
        Returns:
            List[Union[Condition, FilterGroup]]: 解析后的条件列表
            
        Raises:
            ValueError: 如果条件字符串格式错误或无法解析
        """
        try:
            if not condition_str or condition_str.strip() == '':
                return []
                
            # 清理和标准化输入字符串
            cleaned_str = condition_str.strip()
            
            # 解析SQL语句
            parsed = sqlparse.parse(cleaned_str)
            if not parsed:
                return []
                
            statement = parsed[0]
            
            # 提取tokens并解析
            conditions = []
            QueryConditionParser._parse_tokens(statement.tokens, conditions)
            
            return conditions
            
        except Exception as e:
            logger.error(f"解析条件字符串失败: {condition_str}, 错误: {e}")
            raise ValueError(f"条件字符串解析失败: {str(e)}")
    
    @staticmethod
    def _parse_tokens(tokens, conditions: List[Union[Condition, FilterGroup]], 
                     current_operator: LogicalOperator = LogicalOperator.AND) -> None:
        """递归解析tokens"""
        i = 0
        while i < len(tokens):
            token = tokens[i]
            
            if hasattr(token, 'is_whitespace') and token.is_whitespace:
                i += 1
                continue
                
            # 处理括号表达式（嵌套条件组）
            if isinstance(token, Parenthesis):
                group_conditions = []
                QueryConditionParser._parse_tokens(token.tokens, group_conditions, current_operator)
                
                if group_conditions:
                    # 检查括号前的逻辑操作符
                    prev_operator = QueryConditionParser._get_previous_logical_operator(tokens, i)
                    group = FilterGroup(
                        operator=prev_operator or LogicalOperator.AND,
                        conditions=group_conditions
                    )
                    conditions.append(group)
                i += 1
                continue
                
            # 处理比较表达式
            if isinstance(token, Comparison):
                condition = QueryConditionParser._parse_comparison(token)
                if condition:
                    conditions.append(condition)
                i += 1
                continue
                
            # 处理逻辑操作符
            if hasattr(token, 'ttype') and token.ttype is Keyword and token.value.lower() in ('and', 'or'):
                current_operator = QueryConditionParser.LOGICAL_OPERATOR_MAP[token.value.lower()]
                i += 1
                continue
                
            # 处理其他token类型（可能是字段名或值）
            if hasattr(token, 'tokens'):
                QueryConditionParser._parse_tokens(token.tokens, conditions, current_operator)
                
            # 尝试检测和解析简单的比较表达式（字段 操作符 值）
            # 查找接下来的非空白token
            next_non_whitespace = []
            j = i
            while j < len(tokens) and len(next_non_whitespace) < 3:
                token_j = tokens[j]
                if not (hasattr(token_j, 'is_whitespace') and token_j.is_whitespace):
                    next_non_whitespace.append((j, token_j))
                j += 1
                
            if len(next_non_whitespace) == 3:
                (i1, left), (i2, operator_token), (i3, right) = next_non_whitespace
                # 检查是否构成有效的比较表达式
                if (hasattr(left, 'value') and hasattr(operator_token, 'value') and 
                    hasattr(right, 'value') and operator_token.value.strip() in QueryConditionParser.OPERATOR_MAP):
                    
                    # 手动构建比较表达式
                    condition = QueryConditionParser._parse_simple_comparison(left, operator_token, right)
                    if condition:
                        conditions.append(condition)
                        i = i3  # 跳到最后一个已处理的token
                
            i += 1
    
    @staticmethod
    def _parse_simple_comparison(left, operator_token, right) -> Optional[Condition]:
        """解析简单的比较表达式（非Comparison对象）"""
        try:
            # 解析字段名
            field_name = QueryConditionParser._extract_identifier(left)
            if not field_name:
                return None
                
            # 解析操作符
            operator_str = operator_token.value.strip().lower()
            operator = QueryConditionParser.OPERATOR_MAP.get(operator_str)
            if not operator:
                logger.warning(f"不支持的操作符: {operator_str}")
                return None
                
            # 解析值
            value = QueryConditionParser._extract_value(right)
            
            # 处理特殊操作符
            if operator in [FilterOperator.IS_NULL, FilterOperator.IS_NOT_NULL]:
                value = None
                
            return Condition(field=field_name, operator=operator, value=value)
            
        except Exception as e:
            logger.error(f"解析简单比较表达式失败: {left} {operator_token} {right}, 错误: {e}")
            return None
    
    @staticmethod
    def _parse_comparison(comparison: Comparison) -> Optional[Condition]:
        """解析比较表达式"""
        try:
            # 提取字段名、操作符和值
            # 找到非空白token
            tokens = [t for t in comparison.tokens if not (hasattr(t, 'is_whitespace') and t.is_whitespace)]
            
            if len(tokens) < 3:
                return None
                
            left, operator_token, right = tokens[0], tokens[1], tokens[2]
            
            # 解析字段名
            field_name = QueryConditionParser._extract_identifier(left)
            if not field_name:
                return None
                
            # 解析操作符
            operator_str = operator_token.value.strip().lower()
            operator = QueryConditionParser.OPERATOR_MAP.get(operator_str)
            if not operator:
                logger.warning(f"不支持的操作符: {operator_str}")
                return None
                
            # 解析值
            value = QueryConditionParser._extract_value(right)
            
            # 处理特殊操作符
            if operator in [FilterOperator.IS_NULL, FilterOperator.IS_NOT_NULL]:
                value = None
                
            return Condition(field=field_name, operator=operator, value=value)
            
        except Exception as e:
            logger.error(f"解析比较表达式失败: {comparison}, 错误: {e}")
            return None
    
    @staticmethod
    def _extract_identifier(token) -> Optional[str]:
        """提取标识符（字段名）"""
        if isinstance(token, Identifier):
            return token.get_real_name()
        elif hasattr(token, 'value'):
            return token.value.strip()
        return None
    
    @staticmethod
    def _extract_value(token) -> Any:
        """提取值"""
        if hasattr(token, 'ttype'):
            if token.ttype in (String.Single, String.Symbol):
                # 字符串值，去除引号
                value = token.value.strip()
                if (value.startswith("'") and value.endswith("'")) or \
                   (value.startswith('"') and value.endswith('"')):
                    return value[1:-1]
                return value
                
            elif token.ttype in (Number.Integer, Number.Float):
                # 数字值
                try:
                    if '.' in token.value:
                        return float(token.value)
                    else:
                        return int(token.value)
                except ValueError:
                    return token.value
                    
            elif token.ttype is Keyword and token.value.lower() in ('true', 'false'):
                # 布尔值
                return token.value.lower() == 'true'
                
            elif token.ttype is Keyword and token.value.lower() == 'null':
                # NULL值
                return None
                
        if hasattr(token, 'value'):
            # 其他类型的值
            value = token.value.strip()
            
            # 检查是否是带引号的字符串（Identifier类型）
            if (value.startswith("'") and value.endswith("'")) or \
               (value.startswith('"') and value.endswith('"')):
                return value[1:-1]
            
            # 尝试解析为数字
            try:
                if '.' in value:
                    return float(value)
                else:
                    return int(value)
            except ValueError:
                # 尝试解析为布尔值
                if value.lower() in ('true', 'false'):
                    return value.lower() == 'true'
                # 返回原始字符串
                return value
                
        return None
    
    @staticmethod
    def _get_previous_logical_operator(tokens, current_index: int) -> Optional[LogicalOperator]:
        """获取前一个逻辑操作符"""
        for i in range(current_index - 1, -1, -1):
            token = tokens[i]
            if hasattr(token, 'is_whitespace') and token.is_whitespace:
                continue
            if hasattr(token, 'value'):
                value = token.value.lower().strip()
                if value in ('and', 'or'):
                    return QueryConditionParser.LOGICAL_OPERATOR_MAP[value]
        return None


# 工具函数：直接解析字符串为GenericFilter
def parse_to_generic_filter(condition_str: str, **kwargs) -> GenericFilter:
    """
    将SQL-like条件字符串解析为GenericFilter对象
    
    Args:
        condition_str: SQL-like条件字符串
        **kwargs: 其他GenericFilter参数
        
    Returns:
        GenericFilter: 解析后的过滤器对象
    """
    from src.models.generic_filter import GenericFilter
    
    conditions = QueryConditionParser.parse_condition_string(condition_str)
    return GenericFilter(conditions=conditions, **kwargs)
